<?php
class omeanalysts_mdl_ome_deliveryexpressfeeSum extends dbeav_model{

    //var $has_export_cnf = true;

    var $export_name = '应收及应付快递费用核对汇总表';

    public function table_name($real=false){
        $table_name = 'ome_delivery';
        if($real){
            return kernel::database()->prefix.$table_name;
        }else{
            return $table_name;
        }
    }

    public function count($filter=null){

        $where = $this->_filter($filter); 

            $JStypes = explode(",", $filter['JStype']);
            if(isset($JStypes) && $JStypes){
                $jst = ' and f.JSType in (\''.implode('\',\'', $JStypes).'\')';
            }
            if($filter['JStype']=='0')
            {              
                $apjst = " and f.JSType in ('0') ";
            }else{
                $apjst = " and 1=2 ";
            }
//             $sql = "SELECT * from (select t.AXCustomer,t.customername,t.JSType,t.AXsupplierCode,t.AXsupplierName,t.saleqty,t.arfee,0 lmfee from (
// SELECT  f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.delivery_cost_actual),0) arfee
//                 from  sdb_wms_deliverycomp a 
//                 inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id
//                 inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id
//                 inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn
//                 inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id
//                 inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn )
//                 inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id )
//                 inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id)
//                 inner join sdb_ome_axsupplier ax on ax.logi_name=e.logi_id and e.delivery_time between ax.effective_time and ax.disable_time
//                 inner join sdb_ome_shop f on e.shop_id=f.shop_id
//                 where  ".$where.$jst." 
//                  group by f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname)t )tt
// union 
// select * from (select t1.AXCustomer,t1.customername,'' jstype,'' AXsupplierCode,'' AXsupplierName,t1.saleqty,0 arfee,t1.lmfee from (
// SELECT ifnull(f.AXCustomer,'no') AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.lmfee),0) lmfee
//                 from  sdb_wms_deliverycomp a 
//                 inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id
//                 inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id
//                 inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn  
//                 inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id
//                 inner join sdb_ome_shop f on e.shop_id = f.shop_id
//                 inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn )
//                 inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id )
//                 inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id)
//                 inner JOIN sdb_material_basic_material_ext ext on items.product_id=ext.bm_id 
//                 where ".$where.$apjst." and f.AXCustomer!='' and items.number>0
//                 group by f.AXCustomer,d.ax_shopname)t1)t11 ";
        $sql="  SELECT tt.AXCustomer,tt.customername,tt.JSType,tt.AXsupplierCode,tt.AXsupplierName,tt.saleqty,tt.arfee,t11.lmfee from (
                  SELECT t.AXCustomer,t.customername,t.JSType,t.AXsupplierCode,t.AXsupplierName,t.saleqty,t.arfee,0 lmfee from ( 
                    SELECT f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.delivery_cost_actual),0) arfee 
                    from sdb_wms_deliverycomp a inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id 
                    inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn 
                    inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn ) 
                    inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id ) inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id) 
                    inner join sdb_ome_axsupplier ax on ax.logi_name=e.logi_id and e.delivery_time between ax.effective_time and ax.disable_time 
                    inner join sdb_ome_shop f on e.shop_id=f.shop_id 
                    where ".$where.$jst." and f.AXCustomer!='' and items.number>0 
                  group by f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname)t 
                )tt left join (
                  SELECT t1.AXCustomer,t1.customername,'0' JSType,t1.AXsupplierCode,t1.AXsupplierName,t1.saleqty,0 arfee,t1.lmfee from ( 
                    SELECT ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.lmfee),0) lmfee 
                      from sdb_wms_deliverycomp a inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id 
                    inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn 
                    inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn ) 
                    inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id ) inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id) 
                    inner join sdb_ome_axsupplier ax on ax.logi_name=e.logi_id and e.delivery_time between ax.effective_time and ax.disable_time 
                    inner join sdb_ome_shop f on e.shop_id=f.shop_id 
                    where  ".$where.$apjst." and f.AXCustomer!='' and items.number>0 
                    group by ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname)t1
                )t11 on tt.AXCustomer=t11.AXCustomer and tt.customername=t11.customername and tt.AXsupplierCode=t11.AXsupplierCode and tt.AXsupplierName=t11.AXsupplierName and tt.JSType=t11.JSType";
        $row = $this->db->select($sql);

        return count($row);
    }

    public function getlist($cols='*', $filter=array(), $offset=0, $limit=-1, $orderType=null){
  

        if(!$filter['time_from'] && !$filter['time_to']){
            return false;
        }else{
            $where = $this->_filter($filter); 

            $JStypes = explode(",", $filter['JStype']);
            if(isset($JStypes) && $JStypes){
                $jst = ' and f.JSType in (\''.implode('\',\'', $JStypes).'\')';
            }
            if($filter['JStype']=='0')
            {              
                $apjst = " and f.JSType in ('0') ";
            }else{
                $apjst = " and 1=2 ";
            }
              $sql="  SELECT tt.AXCustomer,tt.customername,tt.JSType,tt.AXsupplierCode,tt.AXsupplierName,tt.saleqty,tt.arfee,t11.lmfee from (
                  SELECT t.AXCustomer,t.customername,t.JSType,t.AXsupplierCode,t.AXsupplierName,t.saleqty,t.arfee,0 lmfee from ( 
                    SELECT f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.delivery_cost_actual),0) arfee 
                    from sdb_wms_deliverycomp a inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id 
                    inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn 
                    inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn ) 
                    inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id ) inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id) 
                    inner join sdb_ome_axsupplier ax on ax.logi_name=e.logi_id and e.delivery_time between ax.effective_time and ax.disable_time 
                    inner join sdb_ome_shop f on e.shop_id=f.shop_id 
                    where ".$where.$jst." and f.AXCustomer!='' and items.number>0 
                  group by f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname)t 
                )tt left join (
                  SELECT t1.AXCustomer,t1.customername,'0' JSType,t1.AXsupplierCode,t1.AXsupplierName,t1.saleqty,0 arfee,t1.lmfee from ( 
                    SELECT ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.lmfee),0) lmfee 
                      from sdb_wms_deliverycomp a inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id 
                    inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn 
                    inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn ) 
                    inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id ) inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id) 
                    inner join sdb_ome_axsupplier ax on ax.logi_name=e.logi_id and e.delivery_time between ax.effective_time and ax.disable_time 
                    inner join sdb_ome_shop f on e.shop_id=f.shop_id 
                    where ".$where.$apjst." and f.AXCustomer!='' and items.number>0 
                    group by ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname)t1
                )t11 on tt.AXCustomer=t11.AXCustomer and tt.customername=t11.customername and tt.AXsupplierCode=t11.AXsupplierCode and tt.AXsupplierName=t11.AXsupplierName and tt.JSType=t11.JSType";
            $rows = $this->db->selectLimit($sql,$limit,$offset);
        $status_arr=array(
                    '0' => '代收代付',
                    '1' => '理文支付',
                  );
            foreach ($rows as $key => $value) 
            {
                    $rows[$key]['JSType']=$status_arr[$rows[$key]['JSType']];
            }
            return $rows;
        }
        
    }
    
    public function searchOptions(){
        $parentOptions = parent::searchOptions();
        $childOptions = array();
        return $Options = array_merge($parentOptions,$childOptions);
    }

    public function _filter($filter,$tableAlias=null,$baseWhere=null)
    {


        $where = array(1);
       if(empty($filter['time_from']))
        {
          $where[]=' 1=2 ';
        }
        if(isset($filter['time_from']) && $filter['time_from']){
            $where[] = ' a.deliverycomp_time >='.strtotime($filter['time_from']);
        }
        if(isset($filter['time_to']) && $filter['time_to']){
            $where[] = ' a.deliverycomp_time <'.(strtotime($filter['time_to'])+86399);
        }
        if(isset($filter['AXCustomer']) && $filter['AXCustomer']){
            $where[]= ' f.AXCustomer  =\''.addslashes($filter['AXCustomer']).'\'';
        }  
        if(isset($filter['axcustomercode']) && $filter['axcustomercode']){
            $where[]= ' f.AXCustomer  =\''.addslashes($filter['axcustomercode']).'\'';
        }
        if(isset($filter['soprojecter']) && $filter['soprojecter']){
            $where[]= ' f.soprojecter  =\''.addslashes($filter['soprojecter']).'\'';

        }

        return implode($where,' AND ');
    }

    public function get_schema()
    {
        $schema = array (
            'columns' => array (
                'AXCustomer' =>
                array (
                  'type' => 'varchar(255)',
                  'required' => true,
                  'label' => '客户编码',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '120',
                ),
                'customername' =>
                array (
                  'type' => 'varchar(255)',
                  'label' => '客户名称',
                  'editable' => false,
                  'searchtype' => 'has',
                  'filtertype' => 'normal',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '70',
                ),
                'JSType' =>
                array (
                  'type' => 'varchar(32)',
                  'required' => true,
                  'label' => '结算方式',
                  'comment' => '结算方式',
                  'editable' => false,
                  'width' =>140,
                  'searchtype' => 'has',
                  'filtertype' => 'yes',
                  'filterdefault' => true,
                  'in_list' => true,
                  'default_in_list' => true,
                  'is_title' => true,
                  'width' => '100',
                ),
                'AXsupplierCode' =>
                array(
                    'type' => 'varchar(32)',
                    'required' => true,
                    'label' => '供应商编码',
                    'is_title' => true,
                    'width' => 125,
                    'searchtype' => 'nequal',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'in_list' => true,
                    'default_in_list' => false,
                ),
                'AXsupplierName' =>
                array(
                    'type' => 'varchar(64)',
                    'required' => true,
                    'label' => '供应商名称',
                    'is_title' => true,
                    'width' => 125,
                    'searchtype' => 'nequal',
                    'editable' => false,
                    'filtertype' => 'normal',
                    'filterdefault' => true,
                    'in_list' => true,
                    'default_in_list' => false,
                ),
                'saleqty' =>
                array (
                  'type' => 'number',
                  'label' => '出货量（件）',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                  'width' => '80',
                ),
                'arfee' =>
                array (
                  'type' => 'money',
                  'label' => '应付快递费',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                  'width' => '80',
                ),
                'lmfee' =>
                array (
                  'type' => 'money',
                  'label' => '应收快递费',
                  'required' => true,
                  'default' => 0,
                  'editable' => false,
                  'width' => '80',
                ),
            ),
            'idColumn' => 'name',
            'in_list' => array (
                0 => 'AXCustomer',
                1 => 'customername',
                2 => 'JSType',
                3 => 'AXsupplierCode',
                4 => 'AXsupplierName',
                5 => 'saleqty',
                6 => 'arfee',
                7 => 'lmfee',
            ),
            'default_in_list' => array (
                0 => 'AXCustomer',
                1 => 'customername',
                2 => 'JSType',
                3 => 'AXsupplierCode',
                4 => 'AXsupplierName',
                5 => 'saleqty',
                6 => 'arfee',
                7 => 'lmfee',
            ),

        );
        return $schema;
    }

    /**
     * 获得日志类型(non-PHPdoc)
     * @see dbeav_model::getLogType()
     */
    public function getLogType($logParams) {
        $type = $logParams['type'];
        $logType = 'none';
        if ($type == 'export') {
            $logType = $this->exportLogType($logParams);
        }
        elseif ($type == 'import') {
            $logType = $this->importLogType($logParams);
        }
        return $logType;
    }
    /**
     * 导出日志类型
     * @param Array $logParams 日志参数
     */
    public function exportLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_export';
        return $type;
    }
    /**
     * 导入操作日志类型
     * @param Array $logParams 日志参数
     */
    public function importLogType($logParams) {
        $params = $logParams['params'];
        $type = 'report';
        if ($logParams['app'] == 'omeanalysts' && $logParams['ctl'] == 'ome_delivery') {
            $type .= '_purchaseReport_expressAnalysis';
        }
        $type .= '_import';
        return $type;
    }

    function io_title( $filter, $ioType='csv' )
    {

        switch( $filter ){
            case 'delivery':
                $this->oSchema['csv'][$filter] = array(
                    '*:客户编码' => 'AXCustomer',
                    '*:客户名称' => 'customername',
                    '*:结算方式' => 'JSType',
                    '*:供应商编码' => 'AXsupplierCode',
                    '*:供应商名称' => 'AXsupplierName',
                    '*:出货量（件）' => 'qty',
                    '*:应付快递费' => 'flatFeePrice',
                    '*:应收快递费' => 'lmfee',
                );
                break;
        }
        $this->ioTitle[$ioType][$filter] = array_keys( $this->oSchema[$ioType][$filter] );
        return $this->ioTitle[$ioType][$filter];
    }

    //根据查询条件获取导出数据
    public function getExportDataByCustom($fields, $filter, $has_detail, $curr_sheet, $start, $end){
          //根据选择的字段定义导出的第一行标题
          if($curr_sheet == 1){

              $title = array();
              foreach($this->io_title('delivery') as $k => $v ){
                  $title[] = mb_convert_encoding($v, 'GBK', 'UTF-8');
              }

              $data['content']['main'][0] = '"'.implode('","',$title).'"';
          }

          $where = $this->_filter($filter);
          
            $JStypes = explode(",", $filter['JStype']);
            if(isset($JStypes) && $JStypes){
                $jst = ' and f.JSType in (\''.implode('\',\'', $JStypes).'\')';
            }
            if($filter['JStype']=='0' || $filter['JStype']=='0,1')
            {              
                $apjst = " and f.JSType in ('0') ";
            }else{
                $apjst = " and 1=2 ";
            }
              $sql="  SELECT tt.AXCustomer,tt.customername,tt.JSType,tt.AXsupplierCode,tt.AXsupplierName,tt.saleqty,tt.arfee,t11.lmfee from (
                  SELECT t.AXCustomer,t.customername,t.JSType,t.AXsupplierCode,t.AXsupplierName,t.saleqty,t.arfee,0 lmfee from ( 
                    SELECT f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.delivery_cost_actual),0) arfee 
                    from sdb_wms_deliverycomp a inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id 
                    inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn 
                    inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn ) 
                    inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id ) inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id) 
                    inner join sdb_ome_axsupplier ax on ax.logi_name=e.logi_id and e.delivery_time between ax.effective_time and ax.disable_time 
                    inner join sdb_ome_shop f on e.shop_id=f.shop_id 
                    where ".$where.$jst." and f.AXCustomer!='' and items.number>0 
                  group by f.JSType,ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname)t 
                )tt left join (
                  SELECT t1.AXCustomer,t1.customername,'0' JSType,t1.AXsupplierCode,t1.AXsupplierName,t1.saleqty,0 arfee,t1.lmfee from ( 
                    SELECT ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname customername,IFNULL(sum(items.number),0) saleqty,IFNULL(sum(e.lmfee),0) lmfee 
                      from sdb_wms_deliverycomp a inner join sdb_wms_deliverycomp_line b on a.id = b.deliverycomp_id 
                    inner join sdb_wms_deliverynote_line d on b.deliverynote_id = d.deliverynote_id inner join sdb_wms_delivery e on d.delivery_bn = e.delivery_bn 
                    inner join sdb_wms_delivery_items items on e.delivery_id=items.delivery_id inner JOIN sdb_ome_delivery od on ( e.outer_delivery_bn = od.delivery_bn ) 
                    inner JOIN sdb_ome_delivery_order odo on ( od.delivery_id = odo.delivery_id ) inner JOIN sdb_ome_orders os on (odo.order_id = os.order_id) 
                    inner join sdb_ome_axsupplier ax on ax.logi_name=e.logi_id and e.delivery_time between ax.effective_time and ax.disable_time 
                    inner join sdb_ome_shop f on e.shop_id=f.shop_id 
                    where  ".$where.$apjst." and f.AXCustomer!='' and items.number>0 
                    group by ax.AXsupplierCode,ax.AXsupplierName,f.AXCustomer,d.ax_shopname)t1
                )t11 on tt.AXCustomer=t11.AXCustomer and tt.customername=t11.customername and tt.AXsupplierCode=t11.AXsupplierCode and tt.AXsupplierName=t11.AXsupplierName and tt.JSType=t11.JSType";
            $omedelivery = $this->db->selectLimit($sql,$end,$start);
              $status_arr=array(
                    '0' => '代收代付',
                    '1' => '理文支付',
                  );
            foreach ($omedelivery as $key => $value) 
            {
                    $omedelivery[$key]['JSType']=$status_arr[$omedelivery[$key]['JSType']];
            }
            foreach ($omedelivery as $k => $aFilter) {
                $exptmp_data = array();
                foreach ($aFilter as $col => $val) {
                    $aFilter[$col] = mb_convert_encoding($aFilter[$col], 'GBK', 'UTF-8');
                    $exptmp_data[] = $aFilter[$col];
                }

                $data['content']['main'][] = implode(',', $exptmp_data);
            }
            return $data;
        
    }
}
